Slow queries in PL/PGSQL function
От | Jim Crate |
---|---|
Тема | Slow queries in PL/PGSQL function |
Дата | |
Msg-id | r02010100-1032-5E4696A2632F11D8B8E90003939CD378@[63.175.177.240] обсуждение исходный текст |
Ответы |
Re: Slow queries in PL/PGSQL function
|
Список | pgsql-general |
I have a couple queries in a PL/PGSQL function which execute very slowly (around one minute each) which execute in .5 second when not executed from within the function. Is there any way to determine why this is happening? I couldn't figure out how to run EXPLAIN ANALYZE from within the function. The queries aren't very complicated. Here is an example query and plan. About 240K rows in x_rbl_ips, 7.5M rows in filter_ips. explain analyze SELECT DISTINCT i_ip FROM x_rbl_ips LEFT JOIN filter_ips ON x_rbl_ips.i_ip = filter_ips.i_filter_ip WHERE x_rbl_ips.dts_last_modified > '2004-02-18 22:24:15.901689+00' AND filter_ips.i_filter_ip IS NOT NULL AND (i_filter_type_flags & X'02000000'::integer) <> X'02000000'::integer QUERY PLAN Unique (cost=5703.70..5703.81 rows=23 width=4) (actual time=524.439..524.439 rows=0 loops=1) -> Sort (cost=5703.70..5703.75 rows=23 width=4) (actual time=524.438..524.438 rows=0 loops=1) Sort Key: x_rbl_ips.i_ip -> Nested Loop (cost=0.00..5703.18 rows=23 width=4) (actual time=524.028..524.028 rows=0 loops=1) -> Seq Scan on x_rbl_ips (cost=0.00..5616.56 rows=23 width=4) (actual time=412.738..415.842 rows=1738 loops=1) Filter: (dts_last_modified > '2004-02-18 22:44:15.901689'::timestamp without time zone) -> Index Scan using filter_ips_source_ip on filter_ips (cost=0.00..3.75 rows=1 width=4) (actual time=0.061..0.061 rows=0 loops=1738) Index Cond: ("outer".i_ip = filter_ips.i_filter_ip) Filter: ((i_filter_ip IS NOT NULL) AND ((i_filter_type_flags & 33554432) <> 33554432)) Total runtime: 524.868 ms
В списке pgsql-general по дате отправления: